package org.moore.utils;

import org.moore.model.Score;

import java.sql.*;

// 排行榜工具类
public class ScoreUtils {
    // 插入分数至排行榜的方法
    public static int insertScore(Score score){
        // 反射
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("依赖加载成功！！！"); //测试语句，后续忽略
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("依赖加载失败......"); //测试语句
        }
        System.out.println("开始数据库操作......"); //测试语句
        // JDBC
        // 定义SQL语句
        String sql = "INSERT INTO t_score VAlUES (NULL,?,?,?,?)";
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            // 获取连接
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jf240503db", "Moore", "123456");
            // 创建会话对象 预编译对象
            pst = conn.prepareStatement(sql);
            pst.setInt(1, score.getUserid());
            pst.setInt(2, score.getGameMode());
            pst.setInt(3, score.getGameLevel());
            pst.setInt(4, score.getScore());
            // 使用会话对象，执行sql语句 返回 受影响的条数
            int result = pst.executeUpdate();
            if (result > 0) {
                return result;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return  -1;
    }

    // 展示排行榜的方法
    public static String showScore(int gameMode, int gameLevel) {
        // 创建文本内容
        int count = 0;
        StringBuilder str = new StringBuilder("\t名次\tUID\t昵称\t分数\r\n============================================\r\n");
        // 反射
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        // JDBC
        // 定义SQL语句
        String sql = "SELECT a.user_id,b.name,MAX(a.score) FROM t_score a,t_user b WHERE a.gameMode = '" + gameMode + "' AND a.gameLevel = '" + gameLevel + "' AND a.user_id = b.id GROUP BY a.user_id ORDER BY MAX(a.score) DESC LIMIT 0,10";
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            // 获取连接
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jf240503db", "Moore", "123456");
            // 创建会话对象
            st = conn.createStatement();
            // 使用会话对象，执行sql语句
            // 执行查询语句并返回查询的结果集
            rs = st.executeQuery(sql);
            while (rs.next()) {
                count++;
                // 如果有下一个元素
                int userid = rs.getInt("user_id");
                String name = rs.getString("name");
                int score = rs.getInt("MAX(a.score)");
                str.append("\t").append(count).append("\t").append(userid).append("\t").append(name).append("\t").append(score).append("\r\n");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (rs != null) {
                try {
                    rs.close();
                } catch(SQLException e) {
                    e.printStackTrace();
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch(SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch(SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return str.toString();
    }

}
